DROP TABLE IF EXISTS jms_ods.yl_lmdm_sys_first_code;
CREATE EXTERNAL TABLE jms_ods.yl_lmdm_sys_first_code(
 ID bigint comment "",  --NUMBER(11)
 NAME string comment "集包名称",  --NVARCHAR2(50)
 CODE string comment "集包编码(一段码)",  --NVARCHAR2(50)
 ATTRIBUTES int comment "集包属性(枚举)",  --NUMBER(6)
 TYPE int comment "包类型",  --NUMBER(6)
 CENTER_ID bigint comment "转运中心ID",  --NUMBER(11)
 CENTER_NAME string comment "转运中心名称",  --NVARCHAR2(60)
 CENTER_CODE string comment "转运中心编码",  --NVARCHAR2(20)
 CENTER_PROVINCE_ID bigint comment "末端机构所属省ID",  --NUMBER(11)
 DESTINATION_COUNTRY_ID bigint comment "目的地国家ID",  --NUMBER(11)
 DESTINATION_PROVINCE_ID bigint comment "目的地省份ID",  --NUMBER(11)
 DESTINATION_CITY_ID bigint comment "目的地城市ID",  --NUMBER(11)
 DESTINATION_AREA_ID bigint comment "目的地区县ID",  --NUMBER(11)
 DESTINATION_TOWN_ID bigint comment "目的地乡镇ID",  --NUMBER(11)
 ORIGIN_COUNTRY_ID bigint comment "始发地国家ID",  --NUMBER(11)
 ORIGIN_PROVINCE_ID bigint comment "始发地省份ID",  --NUMBER(11)
 ORIGIN_CITY_ID bigint comment "始发地城市ID",  --NUMBER(11)
 ORIGIN_AREA_ID bigint comment "始发地区县ID",  --NUMBER(11)
 ORIGIN_TOWN_ID bigint comment "始发地乡镇ID",  --NUMBER(11)
 IS_ENABLE int comment "是否启用:1启用,2不启用",  --NUMBER(6)
 IS_DELETE int comment "是否删除:1未删除,2已删除",  --NUMBER(6)
 CREATE_BY bigint comment "创建人ID",  --NUMBER(11)
 UPDATE_BY bigint comment "最后更新人ID",  --NUMBER(11)
 CREATE_BY_NAME string comment "创建人名称",  --NVARCHAR2(50)
 UPDATE_BY_NAME string comment "最后修改人名称",  --NVARCHAR2(50)
 CREATE_TIME timestamp comment "创建时间",  --DATE
 UPDATE_TIME timestamp comment "更新时间",  --DATE
 EFFECT_TIME timestamp comment "生效时间",  --DATE
 INVALID_TIME timestamp comment "失效时间",  --DATE
 VERSION string comment "版本号",  --NVARCHAR2(10)
 SORT int comment "排序",  --NUMBER(6)
 MODIFIED_TIME timestamp comment "记录修改时间",  --DATE
 DEST_CENTER_ID bigint comment '目的中心ID',
 DEST_CENTER_CODE string comment '目的中心编码',
 DEST_CENTER_NAME string comment '目的中心名称';
 )
 COMMENT '一段码维护表'
 PARTITIONED BY (dt STRING COMMENT '更新/创建日期 (yyyy-MM-dd)')
 STORED AS PARQUET
 LOCATION '/dw/hive/jms_ods.db/external/yl_lmdm_sys_first_code'
 TBLPROPERTIES (
 'discover.partitions'='false',
 'parquet.column.index.access'='true'
 );

ALTER TABLE jms_ods.yl_lmdm_sys_first_code
ADD COLUMN `DEST_CENTER_ID` bigint comment '目的中心ID',
ADD COLUMN `DEST_CENTER_CODE` string comment '目的中心编码',
ADD COLUMN `DEST_CENTER_NAME` string comment '目的中心名称';

MSCK REPAIR TABLE jms_ods.yl_lmdm_sys_first_code;
